﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;

namespace EffiProzDBTest
{
    /// <summary>
    /// Summary description for UnitTest1
    /// </summary>
    [TestClass]
    public class UserManagementTest
    {
        SessionInterface sessionProxy;
        String user = "sa";
        String password = "";
        String connType = DatabaseURL.S_MEM;
        //String host = "localhost";
        // int port = 0;
        // String path = "Test";
        String database = "Test";
        string sql = "";       
        HsqlProperties props = new HsqlProperties();

        public UserManagementTest()
        {
            sessionProxy = DatabaseManager.newSession(connType, database,
                        user, password, props);           
       
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void TestCreateUser()
        {

            Result resultIn;
            Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

            string sql = "CREATE USER admin1 PASSWORD pass1 ADMIN";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());           
            Assert.IsFalse(resultIn.isData());


            user = "admin1";
            password = "pass1";
            SessionInterface sessionProxy2 = DatabaseManager.newSession(connType, database,
                        user, password, props); // admin user session

            user = "user1";
            password = "pass1";

            sql = "CREATE USER "+ user+ " PASSWORD " +password+ "";

            resultOut.clear();
            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy2.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());           
            Assert.IsFalse(resultIn.isData());


            SessionInterface sessionProxy3 = DatabaseManager.newSession(connType, database,
                       user, password, props);  // non admin user session


            user = "user2";
            password = "pass2";

            sql = "CREATE USER " + user + " PASSWORD " + password + "";

            resultOut.clear();
            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy3.execute(resultOut); // this should fail

            Assert.IsNotNull(resultIn);  
            Assert.IsTrue(resultIn.isError());
        }

        [TestMethod]
        public void TestAlterUser()
        {
            Result resultIn;
            Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

            user = "adminau1";
            password = "pass1";
            sql = "CREATE USER " + user + " PASSWORD " + password + " ADMIN";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());



            password = "pass1";
            SessionInterface sessionProxy2 = DatabaseManager.newSession(connType, database,
                        user, password, props); // admin user session old pass word

            Assert.IsNotNull(sessionProxy2);

            password = "newpass1";

            sql = "ALTER USER " + user + " SET PASSWORD " + password + "";

            resultOut.clear();
            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());


            SessionInterface sessionProxy3 = DatabaseManager.newSession(connType, database,
                       user, password, props);  //admin user session new password

            Assert.IsNotNull(sessionProxy3);

            password = "pass1";
            SessionInterface sessionProxy4 = null;
            try
            {
                sessionProxy4 = DatabaseManager.newSession(connType, database,
                          user, password, props);  //admin user session old password - SHOULD FAIL
            }
            catch (Exception e)
            {
                Assert.IsTrue(e is HsqlException);
            }

            Assert.IsNull(sessionProxy4);

            sessionProxy2.close();
            sessionProxy3.close();

            sql = "DROP USER " + user + ";";

            resultOut.clear();
            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            
        }


        [TestMethod]
        public void TestRole()
        {
            Result resultIn;
            Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

            string role = "testrole";
            sql = "CREATE ROLE " + role  + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());


            string role2 = "testrole2";
            sql = "CREATE ROLE " + role2 + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());


            user = "userr1";
            password = "pass1";

            sql = "CREATE USER " + user + " PASSWORD " + password + "";

            resultOut.clear();
            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            string user2 = "userr2";
            password = "pass2";

            sql = "CREATE USER " + user2 + " PASSWORD " + password + "";

            resultOut.clear();
            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());         



            sql = "GRANT " + role + " TO " + user +";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            sql = "GRANT " + role+ " , "+ role2 + " TO " + user2 + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());


            sql = "REVOKE " + role + " FROM " + user + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());


            sql = "REVOKE " + role + " , " + role2 + " FROM " + user2 + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());



            sql = "DROP ROLE " + role + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

        }



        [TestMethod]
        public void TestGrantOnTable()
        {
            Result resultIn;
            Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

            string user = "usergt1";
            string password = "pass1";
            string sql = "CREATE USER " + user + " PASSWORD " + password + "";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);
            resultIn = sessionProxy.execute(resultOut);
            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            SessionInterface sessionProxy2 = DatabaseManager.newSession(connType, database,
                        user, password, props); //user session
            Assert.IsNotNull(sessionProxy2);

            string table = TestHelper.GetNewTableName();
            sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int );";
              
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql); // none admin cannot create a table
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            // Test Grant INSERT

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql); // no insert permission

            sql = "GRANT INSERT ON " + table + " TO " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            // Test Grant UPDATE
          
            sql = TestHelper.CreateStdUpdate(table, "col3", 45);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql); // no  permission

            sql = "GRANT UPDATE ON " + table + " TO " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdUpdate(table, "col3", 45);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            // Test Grant SELECT

            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql); // no  permission

            sql = "GRANT SELECT ON " + table + " TO " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy2, sql,null);

            // Test Grant DELETE

            sql = "DELETE FROM "+table+" ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql); // no  permission

            sql = "GRANT DELETE ON " + table + " TO " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

//****************************** REVOKE PHASE  

            //Test Revoke Insert
            sql = "REVOKE INSERT ON " + table + " FROM " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

            // Test Revoke UPDATE

            sql = "REVOKE UPDATE ON " + table + " FROM " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdUpdate(table, "col3", 45);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

            // Test Revoke SELECT

            sql = "REVOKE SELECT ON " + table + " FROM " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 3);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy2, sql);

            // Test Revoke DELETE

            sql = "REVOKE DELETE ON " + table + " FROM " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

 //****************************** GRANT ALL PHASE  

            sql = "GRANT ALL ON " + table + " TO " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            // Test Grant INSERT        
          
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            // Test Grant UPDATE
          
            sql = TestHelper.CreateStdUpdate(table, "col3", 45);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            // Test Grant SELECT
          
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy2, sql, null);

            // Test Grant DELETE

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

//****************************** REVOKE ALL PHASE  

            sql = "REVOKE ALL ON " + table + " FROM " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            // Test Grant INSERT        

            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

            // Test Grant UPDATE

            sql = TestHelper.CreateStdUpdate(table, "col3", 45);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

            // Test Grant SELECT

            sql = TestHelper.CreateStdSelect(table, 3);
           TestHelper.ExecuteSelectSQL_Failure(sessionProxy2, sql);

            // Test Grant DELETE

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

        }

        [TestMethod]
        public void TestGrantOnTableRole()
        {
            Result resultIn;
            Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

            string user = "usergt2";
            string password = "pass1";
            string sql = "CREATE USER " + user + " PASSWORD " + password + "";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string role = "usergt1role1";
            sql = "CREATE ROLE " + role + ";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "GRANT " + role + " TO " + user + ";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            SessionInterface sessionProxy2 = DatabaseManager.newSession(connType, database,
                        user, password, props); //user session
            Assert.IsNotNull(sessionProxy2);

            string table = TestHelper.GetNewTableName();
            sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int );";

            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql); // none admin cannot create a table
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string orginalUser = user;
            user = role; 

            // Test Grant INSERT

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql); // no insert permission

            sql = "GRANT INSERT ON " + table + " TO " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            // Test Grant UPDATE

            sql = TestHelper.CreateStdUpdate(table, "col3", 45);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql); // no  permission

            sql = "GRANT UPDATE ON " + table + " TO " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdUpdate(table, "col3", 45);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            // Test Grant SELECT

            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql); // no  permission

            sql = "GRANT SELECT ON " + table + " TO " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy2, sql, null);

            // Test Grant DELETE

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql); // no  permission

            sql = "GRANT DELETE ON " + table + " TO " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            //****************************** REVOKE PHASE  

            //Test Revoke Insert
            sql = "REVOKE INSERT ON " + table + " FROM " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

            // Test Revoke UPDATE

            sql = "REVOKE UPDATE ON " + table + " FROM " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdUpdate(table, "col3", 45);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

            // Test Revoke SELECT

            sql = "REVOKE SELECT ON " + table + " FROM " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 3);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy2, sql);

            // Test Revoke DELETE

            sql = "REVOKE DELETE ON " + table + " FROM " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

            //****************************** GRANT ALL PHASE  

            sql = "GRANT ALL ON " + table + " TO " + user + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            // Test Grant INSERT        

            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            // Test Grant UPDATE

            sql = TestHelper.CreateStdUpdate(table, "col3", 45);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

            // Test Grant SELECT

            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy2, sql, null);

            // Test Grant DELETE

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy2, sql);

           


            //****************************** REVOKE ALL PHASE  

            //Revoke role just to check
            sql = "REVOKE " + role + " FROM " + orginalUser + ";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            // Test Grant INSERT        

            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

            // Test Grant UPDATE

            sql = TestHelper.CreateStdUpdate(table, "col3", 45);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

            // Test Grant SELECT

            sql = TestHelper.CreateStdSelect(table, 3);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy2, sql);

            // Test Grant DELETE

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy2, sql);

        }


        [TestMethod]
        public void TestConnectDisconnect()
        {
            Result resultIn;
            Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

            string user = "usercd1";
            string password = "pass1";
            string sql = "CREATE USER " + user + " PASSWORD " + password + "";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table = TestHelper.GetNewTableName();
            sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int );";      
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val, col3val);

            //now connect as user
            sql = "CONNECT USER " + user + " PASSWORD "+password+" ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 3);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy, sql); // no permission - should fail
            Assert.IsFalse(sessionProxy.isClosed());

            sql = "DISCONNECT ; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 3);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy, sql); // ALL Connections clossed
            Assert.IsTrue(sessionProxy.isClosed());
        
        }

    }
}
